package com.ten_4_4.hiring.company.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.ten_4_4.hiring.comm.PageModel;
import com.ten_4_4.hiring.comm.util.DBUtil;
import com.ten_4_4.hiring.company.po.PositionPo;

/**
 * Position Dao
 *
 * @author Zzy.
 * @since 2016年1月14日
 */
public class PositionDao {

	/**
	 * 发布职位
	 * 
	 * @param position
	 * @return
	 * @throws RuntimeException
	 */
	public int add(PositionPo position) {
		List<Object> params = new ArrayList<>();
		params.add(position.getCompanyId());
		params.add(position.getName());
		params.add(position.getDuty());
		params.add(position.getAbility());
		params.add(position.getSalary1());
		params.add(position.getSalary2());
		
		params.add(position.getEducation());
		params.add(position.getWorkTime1());
		params.add(position.getWorkTime2());
		int row = DBUtil.executeUpdate(
				"INSERT INTO position VALUES(NULL ,?,?,?,?,?,?,NOW(),?,?,?)", params);
		return row;
	}

	/**
	 * 查询所有的职位
	 * 
	 * @return
	 */
	public List<PositionPo> list() {
		String sql = "SELECT c.name as 'company$name', c.address as 'company$address', c.slogan as 'company$slogan', p.id, p.name, p.salary1, p.salary2, p.release_time FROM company c, position p WHERE c.id=p.company_id ORDER BY p.id DESC";
		List<PositionPo> positionList = DBUtil.executeQuery(
				PositionPo.class, sql);
		return positionList;
	}

	/**
	 * 根据职位id查询所对应详细信息
	 * 
	 * @param id
	 * @return
	 */
	public PositionPo detail(int id) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("c.name as 'company$name', ");
		sb.append("c.email as 'company$email', ");
		sb.append("c.phone as 'company$phone', ");
		sb.append("c.linkman as 'company$linkman', ");
		sb.append("c.address as 'company$address', ");
		sb.append("c.numbers as 'company$numbers', ");
		sb.append("c.slogan as 'company$slogan', ");
		sb.append("p.id, ");
		sb.append("p.name, ");
		sb.append("p.duty, ");
		sb.append("p.ability, ");
		sb.append("p.salary1, ");
		sb.append("p.salary2, ");
		sb.append("p.release_time ");
		sb.append("FROM company c, position p ");
		sb.append("WHERE c.id=p.company_id AND p.id=? ");
		return DBUtil.getUniqueResult(PositionPo.class, sb.toString(), id);
	}

	/**
	 * 查询当前登录企业的所有发布职位信息
	 * 
	 * @param companyId
	 * @return
	 */
	public static List<PositionPo> listById(int companyId) {
		List<PositionPo> positionList = DBUtil.executeQuery(
				PositionPo.class,
				"SELECT * FROM position WHERE company_id=? ORDER BY id DESC",
				companyId);
		return positionList;
	}

	/**
	 * 职位删除
	 * 
	 * @param id
	 * @return
	 * @throws RuntimeException
	 */
	public int delete(int id) {
		return DBUtil.executeUpdate("DELETE FROM position WHERE id=?", id);
	}

	/**
	 * 职位详细信息查询
	 * 
	 * @param id
	 * @return
	 */
	public PositionPo get(int id) {
		String sql = "SELECT * FROM position WHERE id=?";
		return DBUtil.getUniqueResult(PositionPo.class, sql, id);
	}

	/**
	 * 执行简历信息修改
	 * 
	 * @param position
	 * @return
	 */
	public int update(PositionPo position) {
		List<Object> params = new ArrayList<>();
		params.add(position.getName());
		params.add(position.getDuty());
		params.add(position.getAbility());
		params.add(position.getSalary1());
		params.add(position.getSalary2());
		params.add(position.getId());
		String sql = "UPDATE position SET name=?, duty=?, ability=?, salary1=?, salary2=? WHERE id=?";
		return DBUtil.executeUpdate(sql, params);
	}

	/*public static List<PositionPo> main() {
		List<PositionPo> positionList = new ArrayList<>();
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT * FROM position";
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				PositionPo positionPo = new PositionPo();
				positionPo.setId(resultSet.getInt("id"));
				positionPo.setCode(resultSet.getString("code"));
				positionPo.setPassword(resultSet.getString("password"));
				positionPo.setUsername(resultSet.getString("username"));
				positionPo.setGender(resultSet.getInt("gender"));
				positionList.add(positionPo);
			}
		} catch (Exception e) {
			e.printStackTrace();
			positionList = Collections.emptyList();
		} finally {
			DBUtil.close(resultSet, preparedStatement, connection);
		}

		return positionList;
	}*/
	
	public PageModel<PositionPo> listPositionPaging(String searchText, int pageNo, int pageSize) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT c.name as 'company$name', c.address as 'company$address', ");
		sb.append("p.id, p.name, p.salary1, p.salary2, p.release_time, p.duty, p.education, p.work_time1, p.work_time2 ");
		sb.append("FROM company c, position p ");
		sb.append("WHERE c.id=p.company_id ");
		if (StringUtils.isNotBlank(searchText)) {
			sb.append("AND p.name LIKE ? ");
		}
		sb.append("LIMIT ?, ? ");
		String sql = sb.toString();
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<PositionPo> list = new ArrayList<>();
		PageModel<PositionPo> pageModel = null;
		try {
			ps = conn.prepareStatement(sql);
			if (StringUtils.isNotBlank(searchText)) {
				ps.setString(1, "%" + searchText + "%");
				ps.setInt(2, (pageNo - 1) * pageSize);
				ps.setInt(3, pageSize);
			} else {
				ps.setInt(1, (pageNo - 1) * pageSize);
				ps.setInt(2, pageSize);
			}
			rs = ps.executeQuery();
			while(rs.next()) {
				PositionPo position = new PositionPo();
				position.setCompany$name(rs.getString("company$name"));
				position.setCompany$address(rs.getString("company$address"));
				position.setId(rs.getInt("id"));
				position.setName(rs.getString("name"));
				position.setSalary1(rs.getInt("salary1"));
				position.setSalary2(rs.getInt("salary2"));
				position.setReleaseTime(rs.getDate("release_time"));
				position.setDuty(rs.getString("duty"));
				position.setEducation(rs.getInt("education"));
				position.setWorkTime1(rs.getInt("work_time1"));
				position.setWorkTime2(rs.getInt("work_time2"));
				list.add(position);
			}
			pageModel = new PageModel<>(list, pageNo, pageSize, getAllPositionCounts(searchText));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		
		return pageModel;
	}

	private int getAllPositionCounts(String searchText) {
		int totalRecords = 0;
		StringBuilder sql = new StringBuilder(
				"SELECT COUNT(*) AS 'totalRecords' FROM position ");
		if (StringUtils.isNotBlank(searchText)) {
			sql.append("WHERE name LIKE ? ");
		}
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connection.prepareStatement(sql.toString());
			if (StringUtils.isNotBlank(searchText)) {
				preparedStatement.setString(1, "%" + searchText + "%");
			}
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			totalRecords = resultSet.getInt("totalRecords");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, preparedStatement, connection);
		}
		return totalRecords;
	}

	public List<PositionPo> listAllPosition() {
		String sql = "SELECT c.name as 'company$name', c.address as 'company$address', c.slogan as 'company$slogan', p.id, p.name, p.salary1, p.salary2, p.release_time, p.duty, p.education, p.work_time1, p.work_time2 FROM company c, position p WHERE c.id=p.company_id";
		List<PositionPo> list = DBUtil.executeQuery(PositionPo.class, sql);
		return list;
	}

	public PositionPo showPositionDetail(int positionId) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT c.name as 'company$name', c.address as 'company$address', c.numbers as 'company$numbers', ");
		sb.append("p.id, p.name, p.salary1, p.salary2, p.release_time, p.duty, p.ability, p.education, p.work_time1, p.work_time2 ");
		sb.append("FROM company c, position p ");
		sb.append("WHERE c.id=p.company_id and p.id = ? ");
		return DBUtil.getUniqueResult(PositionPo.class, sb.toString(), positionId);
	}
}
